package com.express.dao.impl;

import com.express.bean.User;
import com.express.dao.BaseUserDao;
import com.express.util.DruidUtil;
import org.apache.http.client.UserTokenHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: laixiangdong
 * @Date 2020/12/08
 */
public class UserDaoMysql implements BaseUserDao {
    private static final String SQL_CONSOLE = "SELECT COUNT(*) userdatasize, COUNT( TO_DAYS( createtime )= TO_DAYS( NOW()) OR NULL ) userdataday FROM user";
    private static final String SQL_FIND_ALL = "SELECT * FROM user LIMIT ?,?";
    private static final String SQL_FIND_ALL_2 = "SELECT * FROM user";
    private static final String SQL_FIND_BY_PHONE = "SELECT * FROM user WHERE userphone = ?";
    private static final String SQL_INSERT = "INSERT INTO user(username, cardid, userphone, password, createtime) VALUES (?, ?, ?, ?, NOW())";
    private static final String SQL_UPDATE = "UPDATE user SET username = ?, userphone = ?, cardid = ?, password = ? WHERE id = ?";
    private static final String SQL_DELETE = "DELETE FROM user WHERE id=?";

    @Override
    public Map<String, Integer> console() {
        Map<String, Integer> data = new HashMap<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            result = state.executeQuery();
            if (result.next()) {
                int userDataSize = result.getInt("userDataSize");
                int userDataDay = result.getInt("userDataDay");
                data.put("userDataSize", userDataSize);
                data.put("userDataDay", userDataDay);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        List<User> data = new ArrayList<>();
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            if (limit) {
                state = conn.prepareStatement(SQL_FIND_ALL);
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            } else {
                state = conn.prepareStatement(SQL_FIND_ALL_2);
            }
            result = state.executeQuery();
            while (result.next()) {
                int id = result.getInt("id");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String password = result.getString("password");
                Timestamp loginTime = result.getTimestamp("logintime");
                Timestamp createTime = result.getTimestamp("createtime");
                User user = new User(id, userName, password, userPhone, loginTime, createTime, "");
                data.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return data;
    }

    @Override
    public User findByPhone(String phone) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            state.setString(1, phone);
            result = state.executeQuery();
            if (result.next()) {
                int id = result.getInt("id");
                String userName = result.getString("username");
                String userPhone = result.getString("userphone");
                String password = result.getString("password");
                Timestamp loginTime = result.getTimestamp("logintime");
                Timestamp createTime = result.getTimestamp("createtime");
                String cardId = result.getString("cardid");
                return new User(id, userName, password, userPhone, loginTime, createTime, cardId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    @Override
    public Boolean insert(User user) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            state.setString(1, user.getUserName());
            state.setString(2, user.getCardId());
            state.setString(3, user.getUserPhone());
            state.setString(4, user.getPassword());
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return false;
    }

    @Override
    public Boolean update(int id, User newUser) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1, newUser.getUserName());
            state.setString(2, newUser.getUserPhone());
            state.setString(3, newUser.getCardId());
            state.setString(4, newUser.getPassword());
            state.setInt(5, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return false;
    }

    @Override
    public Boolean delete(int id) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1, id);
            return state.executeUpdate() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, null);
        }
        return false;
    }
}
